const { db, query } = require('../database/db')

class User {
    // 用户登录的语句
    static login(username, password, callback) {
        // 最后一个参数callback回调函数
        // 在controller层中接收：bcrypt.hash(password, Mpassword, (err, hash) => { 一一对应的
        let sql1 = `SELECT username, password, id FROM users WHERE username = ?`
        db.query(sql1, [username], (error, result) => {
            if (error) {
                // 回调函数的2个参数：1：错误；2：信息（结果）
                callback(error, null)
            } else {
                // callback(null,result)
                if (result.length > 0) {
                    console.log(result)
                    let sql2 = `
                        SELECT 
                            users.username, 
                            users.id, 
                            roles.id AS role_id,
                            roles.role_code
                        FROM users
                        LEFT JOIN userroles ON userroles.user_id = users.id
                        LEFT JOIN roles ON userroles.role_id = roles.id
                        WHERE users.username = ? AND users.password = ?
                    `
                    db.query(sql2, [username, password], (error, results) => {
                        console.log('miam ', password)
                        if (error) {
                            callback(error, null)
                        } else {
                            console.log('账号密码不一致！上面：', results)
                            if (results.length > 0) {

                                callback(null, results)
                            } else {

                                callback('账号密码不一致！', null)
                            }
                        }
                    })
                } else {
                    callback('此账号不存在！', null)
                }
            }
        })
    }
    static findByPk(id) {
        let sql = `
            SELECT * FROM users WHERE id = ?
        `
        return query(sql, [id])
    }
    static findByUsername(username) {
        let sql = `
            SELECT * FROM users WHERE username = ?
        `
        return query(sql, [username])
    }
    static async create(created_at, username, password, email, role_id) {

        // 1. 创建用户
        const createUserSql = `
                INSERT INTO users (username, password, email, created_at)
                VALUES (?, ?, ?, ?)
            `;
        console.log('euiahhiurogihnbpoi')
        const userResult = await query(createUserSql, [username, password, email, created_at]);
        console.log("userResultuserResultuserResultuserResult>>>>",userResult)
        const userId = userResult.insertId; // 直接获取插入的用户ID
        console.log(userId)
        // 2. 分配角色
        const assignRoleSql = `
                INSERT INTO userroles (user_id, role_id)
                VALUES (?, ?)
            `; 
        return query(assignRoleSql, [userId, role_id]);

    }
    // 
    static async userPermiss(userId) {  // 参数名改为更规范的 userId
        try {
            const sql = `
                SELECT 
                    Roles.id, 
                    Roles.name, 
                    Permissions.name as permission_name, 
                    Permissions.permission_code
                FROM UserRoles
                JOIN Roles ON UserRoles.role_id = Roles.id
                JOIN RolePermissions ON Roles.id = RolePermissions.role_id
                JOIN Permissions ON RolePermissions.permission_id = Permissions.id
                WHERE UserRoles.user_id = ?
            `;

            const roleRows = await query(sql, [userId]);

            if (!roleRows || roleRows.length === 0) {
                return []; // 返回空数组而不是undefined或null
            }

            const userPermissions = roleRows.map(role => role.permission_code);
            return [...new Set(userPermissions)]; // 去重
        } catch (error) {
            console.error('获取用户权限失败:', error);
            throw error; // 或者返回空数组: return [];
        }
    }
    // 用户列表
    static async userlist(userrole, username, page, page_size) {
        // 基础SQL查询语句
        let listSql = `
            SELECT 
                u.id, u.username, u.email, u.created_at, u.updated_at, 
                r.name as role_name, r.id as role_id
            FROM users u
            LEFT JOIN userroles ur ON ur.user_id = u.id      -- 改为 LEFT JOIN
            LEFT JOIN roles r ON ur.role_id = r.id          -- 改为 LEFT JOIN
        `;

        // 初始化参数数组
        let params = [];

        // 构建WHERE条件
        let conditions = [];
        if (username) {
            conditions.push(`u.username LIKE ?`);
            params.push(`%${username}%`);
        }
        if (userrole) {
            conditions.push(`ur.role_id = ?`);
            params.push(userrole);
        }

        // 添加WHERE子句
        if (conditions.length > 0) {
            listSql += ` WHERE ${conditions.join(' AND ')}`;
        }
        // 添加排序（按id降序）
        listSql += ` ORDER BY u.id DESC`;

        // 添加分页
        listSql += ` LIMIT ? OFFSET ?`;
        params.push(page_size);
        params.push((page - 1) * page_size);

        // 计算总数的SQL
        let countSql = `
            SELECT COUNT(*) as count 
            FROM users u
            LEFT JOIN userroles ur ON ur.user_id = u.id      -- 改为 LEFT JOIN
            LEFT JOIN roles r ON ur.role_id = r.id          -- 改为 LEFT JOIN
        `;

        // 添加相同的WHERE条件
        if (conditions.length > 0) {
            countSql += ` WHERE ${conditions.join(' AND ')}`;
        }

        // 执行查询
        const [usersResult, countResult] = await Promise.all([
            query(listSql, params),
            query(countSql, params)
        ]);

        return {
            users: usersResult,
            total: countResult[0].count // 假设countResult返回的是[{count: 123}]
        };
    }
    // 用户的删除----真删除
    static async deleteuser_t(id) {
        let sql = `
            DELETE FROM users WHERE id = ?
        `

        let user_role = `
            DELETE FROM userroles WHERE user_id = ?
        `
        // 先等待关联表的删除成功
        const user_roleResult = await query(user_role, [id])

        return query(sql, [id])
    }
    // 用户的修改--set数据
    static async edit_set(id, username, email, role_id, update_time) {
        let sql = `UPDATE users 
               SET username = ?, email = ?, updated_time = ?
               WHERE id = ?`;
        let user_role = `
            UPDATE userroles 
            SET role_id = ? 
            WHERE user_id = ?
        `
        const [sqlResult, user_roleResult] = await Promise.all([
            query(sql, [username, email, update_time, id]),
            query(user_role, [role_id, id])
        ])
        // let params = [username, email, update_time, id];
        // return query(sql, params);
        return {
            sqlResult,
            user_roleResult
        }
    };
}





module.exports = User